package net.zjitc.homework.homework11.jdbc.book;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 * @Author admin
 * @Date 2019/4/26 8:34
 * 图书管理工具类，用jdbc实现
 * 完成图书的增删改查操作
 */
public class BookDaoDB implements BookDao {

    Connection conn = JDBCUtils.getConnection();
    PreparedStatement pstmt = null;
    ResultSet rs = null;

    /**
     * 将book对象添加到数据库中，返回影响了几行
     *
     * @param
     * @return
     */
    @Override
    public int insert(Book book) {
        Book book1 = query(book.getName());
        if (book1 == null) {
            String sql = "insert into book(name,press,author,price) values(?,?,?,?)";
            try {
                pstmt = conn.prepareStatement(sql);
                pstmt.setString(1, book.getName());
                pstmt.setString(2, book.getPress());
                pstmt.setString(3, book.getAuthor());
                pstmt.setDouble(4, book.getPrice());
                int s = pstmt.executeUpdate();
                return s;
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return 0;
    }

    /**
     * 根据name查询数据库中是否存在该对象，若不存在null
     *
     * @param
     * @return
     */
    @Override
    public Book query(String name) {
        String sql = "select * from book where name=?";
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, name);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                int id = rs.getInt("id");
                String BookName = rs.getString("name");
                String press = rs.getString("press");
                String author = rs.getString("author");
                double price = rs.getDouble("price");
                Book book = new Book(id, name, press, author, price);
                return book;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public void select() {
        String sql = "select * from book";
        try {
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String press = rs.getString("press");
                String author = rs.getString("author");
                double price = rs.getDouble("price");
                System.out.println(id + "\t" + name + "\t" + press + "\t" + author + "\t" + price);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public ArrayList<Book> selectAll() {
        ArrayList<Book> arrayList = new ArrayList<Book>();
        String sql = "select * from book";
        try {
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String press = rs.getString("press");
                String author = rs.getString("author");
                double price = rs.getDouble("price");
                Book book = new Book(id, name, press, author, price);
                arrayList.add(book);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return arrayList;
    }

    @Override
    public int update(Book book1) {
        String sql = "update book set name=?,press=?,author=?,price=? where id=?";
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, book1.getName());
            pstmt.setString(2, book1.getPress());
            pstmt.setString(3, book1.getAuthor());
            pstmt.setDouble(4, book1.getPrice());
            pstmt.setInt(5, book1.getId());
        } catch (Exception e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public int delete(Book book) {
        String sql = "delete from book where id=?";
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, book.getId());
            return pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public void exit() {
        JDBCUtils.close(conn, pstmt, rs);
    }
}
